package sy.service.impl;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import sy.pageModel.BaseEntity;
import sy.pageModel.DataGrid;
import sy.pageModel.DeptPic;
import sy.pageModel.ServiceEntity;
import sy.service.ServiceServiceI;
import sy.util.StringUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by Administrator on 2016-07-05.
 */
@Service("serviceService")
public class ServiceServiceImpl implements ServiceServiceI {
    @Autowired
    private JdbcTemplate template;
    @Override
    public DataGrid findPicDataGird(ServiceEntity service) {
        DataGrid dg = new DataGrid();
        String sql = "select count(*) from s_service s where 1=1   ";
        sql = addWhere(service, sql);//模糊查询
        long total = template.queryForLong(sql);
        dg.setTotal(total);//获取总数
        sql = "SELECT * from s_service s where 1=1";
        sql = addWhere(service, sql);
        sql = addOrderBy(service, sql);
        sql = addLimit(service, sql);
        final List<ServiceEntity> servlceList = new ArrayList<ServiceEntity>();
        template.query(sql, new RowCallbackHandler(){
            public void processRow(ResultSet rs) throws SQLException {
                ServiceEntity entity=new ServiceEntity();
                entity.setId(rs.getString("id"));
                entity.setName(rs.getString("codes"));
                entity.setPid(rs.getString("pid"));
                entity.setContent(rs.getString("content"));
                entity.setIsShow(rs.getString("is_show_title"));
                entity.setPname(rs.getString("id"));
                servlceList.add(entity);
            }});
        dg.setRows(servlceList);
        return dg;
    }

    @Override
    @Transactional
    public int addServiceAction(ServiceEntity service) {
        String pid="".equals(service.getPid())?null:service.getPid();
        String sql="INSERT INTO s_service(id, codes, content, pid, is_show_title) VALUES(?,?,?,?,?)";
        int rows=template.update(sql,new Object[]{StringUtil.getId(),service.getName(),service.getContent(),pid,service.getIsShow()});
        return rows;
    }

    @Override
    @Transactional
    public int editServiceAction(ServiceEntity service) {
        String pid="".equals(service.getPid())?null:service.getPid();
        String sql="UPDATE s_service SET codes=?,content=?,pid=?,is_show_title=? WHERE  id=?";
        int rows=template.update(sql,new Object[]{service.getName(),service.getContent(),pid,service.getIsShow(),service.getId()});
        return rows;
    }

    @Override
    @Transactional
    public int deleteServiceAction(ServiceEntity service) {
        String sql="DELETE FROM s_service WHERE id=?";
        int rows=0;
        String ids[]=service.getIds().split(",");
        for (int i = 0; i <ids.length ; i++) {
            rows=template.update(sql,new Object[]{ids[i]});
        }
        return rows;
    }

    @Override
    public List<ServiceEntity> findBaseList() {
        String sql="select *from s_service";
        final List<ServiceEntity> entities=new ArrayList<>();
        template.query(sql, new Object[]{}, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                ServiceEntity entity=new ServiceEntity();
                entity.setId(resultSet.getString("id"));
                entity.setText(resultSet.getString("codes"));
                entity.setPid(resultSet.getString("pid"));
                entities.add(entity);
            }
        });
        return entities;
    }

    private String addLimit(ServiceEntity pic, String sql) {
        int fromRow = (pic.getPage() - 1) * pic.getRows();
        sql += " limit " + fromRow + "," + pic.getRows();
        return sql;
    }

    private String addWhere(ServiceEntity pic, String sql) {
        if(!StringUtil.isNullOrEmpty(pic.getName())) sql += " and s.codes like '%" + pic.getName() + "%'";
        if(!StringUtil.isNullOrEmpty(pic.getIsShow())) sql += " and s.is_show_title = '" + pic.getIsShow() + "'";
        return sql;
    }


    private String addOrderBy(ServiceEntity pic, String sql) {
        if (pic.getSort() != null) {
            if("name".equals(pic.getSort())) pic.setSort("codes");
            if("pname".equals(pic.getSort())) pic.setSort("id");
            if("pid".equals(pic.getSort())) pic.setSort("pid");
            if("showName".equals(pic.getSort())) pic.setSort("is_show_title");
            sql += " order by " + pic.getSort() + " " + pic.getOrder();
        }
        return sql;
    }
}
